package com.jaks.xpense.data;

import java.util.ArrayList;
import java.util.Calendar;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;

import com.jaks.xpense.data.entities.Expense;
import com.jaks.xpense.data.entities.ExpenseCategory;
import com.jaks.xpense.data.entities.ExpenseTag;
import com.jaks.xpense.data.entities.Income;
import com.jaks.xpense.data.entities.TableEntity;
import com.jaks.xpense.data.entities.Tag;
import com.jaks.xpense.utils.Globals;

public class Expenses extends Table {

	private static final String[] Columns = new String[] { TableEntity.Id, Expense.ExpenseDate,
	    Expense.PaymentType, Expense.Amount, Expense.Comments };

	public Expenses(Context context) throws SQLException {
		super(context);
	}

	@Override
	public long create(TableEntity entity) {
		ContentValues values = entity.getContentValues();
		return db.insert(Expense.TableName, null, values);
	}

	@Override
	public boolean update(TableEntity entity) {
		ContentValues values = entity.getContentValues();
		return db.update(Expense.TableName, values, where(entity.getId()), null) > 0;
	}

	@Override
	public boolean delete(long id) {
		return db.delete(Expense.TableName, where(id), null) > 0;
	}

	@Override
	public Cursor readAll() {
		return db.query(Expense.TableName, Columns, null, null, null, null, Expense.ExpenseDate
		    + " DESC, " + Expense.Id + " DESC");
	}

	@Override
	public TableEntity read(long id) throws SQLException {
		Expense entity = null;

		Cursor cursor = db.query(true, Expense.TableName, Columns, where(id), null, null, null, null,
		    null);

		if (cursor.moveToFirst()) {
			entity = new Expense();
			entity.setId(cursor.getLong(cursor.getColumnIndex(TableEntity.Id)));
			entity.setExpenseDate(cursor.getString(cursor.getColumnIndex(Expense.ExpenseDate)));
			entity.setPaymentType(cursor.getLong(cursor.getColumnIndex(Expense.PaymentType)));
			entity.setAmount(cursor.getDouble(cursor.getColumnIndex(Expense.Amount)));
			entity.setComments(cursor.getString(cursor.getColumnIndex(Expense.Comments)));
		}
		if (cursor != null && !cursor.isClosed()) {
			cursor.close();
		}

		return entity;
	}

	public ArrayList<Tag> getTags(long expenseId) {

		ArrayList<Tag> tags = new ArrayList<Tag>();
		String query = "Select t._id, t.tagOrder, t.name From tags t, expenseTags et Where et.tagId = t._id And et.expenseId = "
		    + expenseId;
		Cursor cursor = db.rawQuery(query, null);

		if ((cursor.getCount() > 0) && cursor.moveToFirst()) {

			do {
				Tag entity = new Tag();
				entity.setId(cursor.getLong(cursor.getColumnIndex(TableEntity.Id)));
				entity.setOrder(cursor.getInt(cursor.getColumnIndex(Tag.Order)));
				entity.setName(cursor.getString(cursor.getColumnIndex(Tag.Name)));

				tags.add(entity);
			} while (cursor.moveToNext());
		}

		if (cursor != null && !cursor.isClosed()) {
			cursor.close();
		}

		return tags;
	}

	public void setTags(long expenseId, long[] tagIds) {

		db.delete(ExpenseTag.TableName, ExpenseTag.ExpenseId + "=" + expenseId, null);

		if (tagIds != null)
			for (long tagId : tagIds) {
				db.insert(ExpenseTag.TableName, null, getExpenseTagContentValues(expenseId, tagId));
			}
	}

	private ContentValues getExpenseTagContentValues(long expenseId, long tagId) {
		ContentValues values = new ContentValues();
		values.put(ExpenseTag.ExpenseId, expenseId);
		values.put(ExpenseTag.TagId, tagId);
		return values;
	}

	public double getBalance() {

		double income = 0, expense = 0;
		String query = "Select Sum(amount) amount From " + Income.TableName;

		Cursor incomeCursor = db.rawQuery(query, null);
		if ((incomeCursor.getCount() > 0) && incomeCursor.moveToFirst()) {
			income = incomeCursor.getDouble(incomeCursor.getColumnIndex(Income.Amount));
		}
		if (incomeCursor != null && !incomeCursor.isClosed()) {
			incomeCursor.close();
		}

		query = "Select Sum(amount) amount From " + Expense.TableName;
		Cursor expenseCursor = db.rawQuery(query, null);
		if ((expenseCursor.getCount() > 0) && expenseCursor.moveToFirst()) {
			expense = expenseCursor.getDouble(expenseCursor.getColumnIndex(Expense.Amount));
		}
		if (expenseCursor != null && !expenseCursor.isClosed()) {
			expenseCursor.close();
		}

		return income - expense;
	}

	public double getTodaysTotal() {
		String today = Globals.toDateText(Calendar.getInstance());
		return getTotalAmount(today, today);
	}

	public double getTotalAmount(String from, String to) {

		double total = 0;
		String query = "Select Sum(amount) amount From " + Expense.TableName + " Where expensedate >= "
		    + Globals.toDbString(from) + " And expensedate <= " + Globals.toDbString(to);

		Cursor cursor = db.rawQuery(query, null);
		if ((cursor.getCount() > 0) && cursor.moveToFirst()) {
			total = cursor.getDouble(cursor.getColumnIndex(Expense.Amount));
		}
		if (cursor != null && !cursor.isClosed()) {
			cursor.close();
		}

		return total;
	}

	public Cursor getFilteredExpenses(String expenseIds) {

		String query = " Select e._id, e.expensedate, e.paymenttype, e.amount, e.comments, p.name From expenses e, paymentTypes p "
		    + " Where e.paymenttype = p._id And e._id in ("
		    + expenseIds
		    + ") Order By e.expensedate DESC, e._id DESC";

		return db.rawQuery(query, null);
	}

	public Cursor getFilteredExpenses(String from, String to, long[] tagIds, long paymentType) {

		String query = " Select Distinct e._id, e.expensedate, e.paymenttype, e.amount, e.comments, p.name From expenses e, paymentTypes p "
		    + " Where e.paymenttype = p._id And e.expensedate >= "
		    + Globals.toDbString(from)
		    + " And e.expensedate <= " + Globals.toDbString(to);

		if (paymentType > 0)
			query += " And e.paymenttype = " + paymentType;

		if (tagIds != null) {
			query = " Select Distinct e._id, e.expensedate, e.paymenttype, e.amount, e.comments, p.name From expenses e, tags t, expenseTags et, paymentTypes p "
			    + " Where e.paymenttype = p._id  And et.expenseId = e._id And et.tagId = t._id And e.expensedate >= "
			    + Globals.toDbString(from)
			    + " And e.expensedate <= "
			    + Globals.toDbString(to)
			    + " And t._id in (" + Globals.toText(tagIds) + ") ";
			if (paymentType > 0)
				query += " And e.paymenttype = " + paymentType;
		}

		query += " Order By e.expensedate DESC, e._id DESC";

		return db.rawQuery(query, null);
	}

	public ArrayList<ExpenseCategory> getFilteredCategories(String from, String to, long[] tagIds,
	    long paymentType) {

		String query = " Create Temp Table tempExpenseIds  As Select Distinct e._id _id From expenses e, tags t, expenseTags et "
		    + " Where et.expenseId = e._id And et.tagId = t._id And e.expensedate >= "
		    + Globals.toDbString(from) + " And e.expensedate <= " + Globals.toDbString(to);

		if (paymentType > 0)
			query += " And e.paymenttype = " + paymentType;

		if (tagIds != null) {
			query += " And t._id in (" + Globals.toText(tagIds) + ") ";
		}

		db.execSQL(query);

		query = " Select e._id, e.amount, t.name From expenses e, tags t, expenseTags et, tempExpenseIds te "
		    + " Where e._id = te._id And et.expenseId = e._id And et.tagId = t._id Order By e._id ASC, t.tagOrder ASC ";

		Cursor cursor = db.rawQuery(query, null);
		ArrayList<ExpenseCategory> categorized = getCategories(cursor);
		if (cursor != null && !cursor.isClosed()) {
			cursor.close();
		}

		query = " Drop Table tempExpenseIds ";
		db.execSQL(query);

		return categorized;
	}

	public ArrayList<ExpenseCategory> getAllCategories() {

		String query = " Select e._id, e.amount, t.name From expenses e, tags t, expenseTags et "
		    + " Where et.expenseId = e._id And et.tagId = t._id Order By e._id ASC, t.tagOrder ASC ";

		Cursor cursor = db.rawQuery(query, null);
		ArrayList<ExpenseCategory> categorized = getCategories(cursor);
		if (cursor != null && !cursor.isClosed()) {
			cursor.close();
		}

		return categorized;
	}

	private ArrayList<ExpenseCategory> getCategories(Cursor cursor) {

		ArrayList<ExpenseCategory> categorized = new ArrayList<ExpenseCategory>();
		if ((cursor.getCount() > 0) && cursor.moveToFirst()) {

			ExpenseCategory categorizing = null;
			boolean isUpdated = false;

			do {
				long expenseId = cursor.getLong(cursor.getColumnIndex(TableEntity.Id));
				String name = cursor.getString(cursor.getColumnIndex(Tag.Name));
				double amount = cursor.getInt(cursor.getColumnIndex(Expense.Amount));

				ExpenseCategory current = new ExpenseCategory(expenseId, name, amount);

				isUpdated = false;
				if (categorizing == null) {
					categorizing = current;
					continue;
				}

				if (current.getExpenseId() == categorizing.getExpenseId()) {
					categorizing.appendName(current.getName());
				} else {

					for (ExpenseCategory category : categorized) {
						if (category.getName().equals(categorizing.getName())) {
							category.appendAmount(categorizing.getExpenseId(), categorizing.getAmount());
							isUpdated = true;
							break;
						}
					}
					if (!isUpdated) {
						categorized.add(categorizing);
					}
					categorizing = current;
				}

			} while (cursor.moveToNext());

			isUpdated = false;
			for (ExpenseCategory category : categorized) {
				if (category.getName().equals(categorizing.getName())) {
					category.appendAmount(categorizing.getExpenseId(), categorizing.getAmount());
					isUpdated = true;
					break;
				}
			}

			if (!isUpdated)
				categorized.add(categorizing);
		}
		return categorized;
	}
}
